package com.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {

	static {
		//加载驱动,驱动只需加载一次
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

//    private  static  String url="jdbc:mysql://175.24.163.193:3306/jerrydb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=PRC";
//    private  static  String username="root";
//    private  static  String password="Mypass123!";

	private static String url = "jdbc:mysql://rm-bp1frruh68hr0f4f0yo.mysql.rds.aliyuncs.com/music?serverTimezone=GMT%2B8";
	private static String username = "lizhenyong";
	private static String password = "Lzy20151511";

	public static Connection getConnection() throws SQLException {
		Connection connection = DriverManager.getConnection(url, username, password);
		return connection;
	}


	/**
	 * 返回插入的自增主键id值
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public static int insertReturnAutoinCrementsId(String sql, Object[] values) throws SQLException {

		Connection connection = null;//声明
		try {
			ThreadLocal<Connection> connectionThreadLocal = SingletonThreadLocal.getInstance().getThreadLocal();//得到一个ThreadLocal对象
			///connection = getConnection();
			connection = connectionThreadLocal.get();//获得当前线程中由ThreadLocal对象中存储 的那个connection对象

			PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					//循环取出values中的数据传入到?号占位符中
					preparedStatement.setObject(i + 1, values[i]);//setXX方法的下标是1开始，数组的下标是从0开始
				}
			}

			int res = preparedStatement.executeUpdate();
			ResultSet rs = preparedStatement.getGeneratedKeys();
//			int songer_id = rs.getInt("songer_id");
			long id = 0L;
			try(ResultSet generatedKeys = preparedStatement.getGeneratedKeys()){
				if(generatedKeys.next()){
					id = generatedKeys.getLong(1);

				}
			};

			return Integer.parseInt(id+"");
		} finally {
			if (connection != null) {
				//  connection.close();
			}
		}
	}

	/**
	 * @param sql    传入insert,update或delete语句
	 * @param values sql语句?号占位符的值列表
	 *               如参数   sql = delete from emp where empno=?   则   vaues=new Object[]{7788}
	 *               如参数   sql = insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)
	 *               则  values = new Object[]{6666,"哈哈","SALES",7788,"2022-01-01",999999.99,10000,10}
	 *               如参数为: sql = delete from emp where deptno=10  则  values=null
	 * @return
	 */
	public static int insertOrUpdateOrDelete(String sql, Object[] values) throws SQLException {

		Connection connection = null;//声明
		try {
			ThreadLocal<Connection> connectionThreadLocal = SingletonThreadLocal.getInstance().getThreadLocal();//得到一个ThreadLocal对象
			///connection = getConnection();
			connection = connectionThreadLocal.get();//获得当前线程中由ThreadLocal对象中存储 的那个connection对象

			//往订单表中写入数据  关闭连接

			//往订单详情表中写入数据  连接已被关闭


			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					//循环取出values中的数据传入到?号占位符中
					preparedStatement.setObject(i + 1, values[i]);//setXX方法的下标是1开始，数组的下标是从0开始
				}
			}

			int res = preparedStatement.executeUpdate();
			return res;
		} finally {
			if (connection != null) {
				//  connection.close();
			}

		}


	}

	/**
	 * @param sql
	 * @param values
	 * @return 返回查询结果，查询结果 的第一行数据存储到一个HashMap对象中，多条数据(多个HashMap) 存储到List集合中做方法返回值
	 */
	public static List<HashMap<String, Object>> queryData(String sql, Object[] values) throws SQLException {
		Connection connection = null;

		try {
			connection = getConnection();

			PreparedStatement preparedStatement = connection.prepareStatement(sql);

			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					preparedStatement.setObject(i + 1, values[i]);
				}
			}

			ResultSet rs = preparedStatement.executeQuery();//得到查询结果
			//得到查询结果的元数据
			ResultSetMetaData metaData = rs.getMetaData();

			int columnCount = metaData.getColumnCount();//得到查询结果的列数

			List<HashMap<String, Object>> tableRowDataMap = new ArrayList<>();

			while (rs.next()) {//循环遍历 结果集中的每一条数据
				HashMap<String, Object> rowData = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {//取出一行中的每一列数据
					String columnName = metaData.getColumnLabel(i).toLowerCase();
					String columnTypeName = metaData.getColumnTypeName(i);//得到列的数据类型 (INT,NUMERIC,DATE,VARCHAR)
					//根据列名称取出该列对应的值,从数据库中取出的列值在java中到底是什么类型我们是未知的，通常字符型(varchar,char)类型都是String类型接收的
					//数值型(int,Numeric等)在java中是用BigDecimal表示的
					//日期类型(Date) 在java中是用 java.util.Date表示
					//最好是根据数据库的数据类型，调用相应的getXXX方法取得具体类型

					Object columnValue = null;
					if (columnTypeName.equalsIgnoreCase("INT")) {
						columnValue = rs.getInt(columnName);
					} else if (columnTypeName.equalsIgnoreCase("NUMERIC")) {
						columnValue = rs.getDouble(columnName);
					} else if (columnTypeName.equalsIgnoreCase("DATE") || columnTypeName.equalsIgnoreCase("DATETIME")) {
						columnValue = rs.getString(columnName);
					} else if (columnTypeName.equalsIgnoreCase("VARCHAR")) {
						columnValue = rs.getString(columnName);
					} else {
						columnValue = rs.getObject(columnName);
					}


					rowData.put(columnName, columnValue);//把列名，列值放到HashMap对象中
				}
				tableRowDataMap.add(rowData);//把每一个HashMap对象放到List集合中
			}

			return tableRowDataMap;//返回List集合，list集合存储了查询的结果


		} finally {
			if (connection != null) {
				connection.close();
			}
		}


	}

	/**
	 * @param sql    传入select语句
	 * @param values 传入 select语句的?号占位符的条件
	 *               如: sql = "select * from emp where deptno=?"  则values=new Object[]{部门号}
	 *               sql = "select * from emp where deptno=? and sal>? "  则values=new Object[]{部门号,工资}
	 *               <p>
	 *               代码封装的思想把变化的部分通过方法的参数传递进来
	 *               取数据的操作肯定是每一个SQL语句都有自己取数据的一段代码,可以利用java的多态机制回调相应的代码
	 * @return 返回 List<Object>集合，返回结果中存储了sql语句的查询结果。
	 * <p>
	 * 应该可以将查询封装成一个公共方法，方法的参数为要执行SQL查询，还有一个查询语句的?号占位符的条件
	 */
	public static <T> List<T> queryData(String sql, Object[] values, RowMapper<T> rowMapper) throws SQLException {
		Connection connection = null;
		try {
			connection = getConnection();
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					preparedStatement.setObject(i + 1, values[i]);
				}
			}
			ResultSet resultSet = preparedStatement.executeQuery();

			List<T> rowDataList = new ArrayList<>();
			while (resultSet.next()) {
				//获得表中的一条记录和JavaBean的映射结果
				T rowData = rowMapper.getRowData(resultSet);//调用rowMapper接口中的方法，回调
				rowDataList.add(rowData);//把查询结果存储到list集合中
			}
			//返回查询结果
			return rowDataList;

		} finally {
			if (connection != null) {
				connection.close();
			}
		}


	}


	/**
	 * @param sql
	 * @param cls
	 * @param currentPage 页码
	 * @param pageSize    每页显示的记录条数
	 * @param <T>
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */

	//形参   sql= 	select * from dd_books where  type_name=?  values="艺术"
	public static <T> PageUtil<T> queryDataByPage(String sql, Class<T> cls, int currentPage, int pageSize, Object... values) throws SQLException, InstantiationException, IllegalAccessException {

		String countSql = "  SELECT count(*) coun from (" + sql + " ) t";

		List<HashMap<String, Object>> countMap = queryData(countSql, values);
		Object counObj = countMap.get(0).get("coun");//取出list集合中的第一个map对象，根据map的key取出map的value
		//总记录数
		int totalCount = Integer.parseInt(String.valueOf(counObj));


		//根据形参values的长度，创建一个长度+2的新数组

		Object[] newArray = new Object[values.length + 2];

		System.arraycopy(values, 0, newArray, 0, values.length);

		String querySql = sql + "   LIMIT ?,?";

		//最后两个元素把limit的参数值填上去
		newArray[newArray.length - 2] = (currentPage - 1) * pageSize;
		newArray[newArray.length - 1] = pageSize;
		//形参values=["艺术",0,15]
		List<T> data = queryData(querySql, cls, newArray);//获得分页查询结果


		// System.out.println("总记录数:"+totalCount);
		//  System.out.println("分页的查询结果为:"+data);


		PageUtil pageUtil = new PageUtil(pageSize, totalCount, currentPage, data);
		//一个方法中有多个数据要返回给调用者 ,把方法的返回类型定义为一个JavaBaan
		return pageUtil;

	}

	/**
	 * 根据sql语句，结合第二个参数class对应的javaBean把查询结果映射到指定的JavaBean
	 * //目前可以处理单表查询操作
	 *
	 * @param sql    传入 sql语句
	 * @param cls    传入JavaBean对应的class
	 * @param values sql语句对应的?号占位符的条件值
	 * @param <T>
	 * @return
	 * @throws SQLException
	 */
	public static <T> List<T> queryData(String sql, Class<T> cls, Object... values) throws SQLException, InstantiationException, IllegalAccessException {
		Connection connection = null;

		try {

			connection = getConnection();

			PreparedStatement preparedStatement = connection.prepareStatement(sql);

			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					preparedStatement.setObject(i + 1, values[i]);
				}
			}

			ResultSet rs = preparedStatement.executeQuery();//得到查询结果
			//得到查询结果的元数据
			ResultSetMetaData metaData = rs.getMetaData();

			int columnCount = metaData.getColumnCount();//得到查询结果的列数

			List<T> tableRowDataMap = new ArrayList<>();//做为方法返回值的集合对象

			while (rs.next()) {//循环遍历 结果集中的每一条数据
				HashMap<String, Object> rowData = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {//取出一行中的每一列数据
					String columnName = metaData.getColumnLabel(i).toLowerCase();
					String columnTypeName = metaData.getColumnTypeName(i);//得到列的数据类型 (INT,NUMERIC,DATE,VARCHAR)

					Object columnValue = null;
					if (columnTypeName.equalsIgnoreCase("INT")) {
						columnValue = rs.getInt(columnName);
					} else if (columnTypeName.equalsIgnoreCase("NUMERIC")) {
						columnValue = rs.getDouble(columnName);
					} else if (columnTypeName.equalsIgnoreCase("DATE") || columnTypeName.equalsIgnoreCase("DATETIME")) {
						columnValue = rs.getString(columnName);
					} else if (columnTypeName.equalsIgnoreCase("VARCHAR")) {
						columnValue = rs.getString(columnName);
					} else {
						columnValue = rs.getObject(columnName);
					}

					// 将数据库中的下划线去掉放入到map中
					rowData.put(columnName.replaceAll("_", ""), columnValue);//把列名，列值放到HashMap对象中
				}


				T t = ReflectUtil.mapValueToBean(rowData, cls);//反射实现代码的封装。
				tableRowDataMap.add(t);

			}

			return tableRowDataMap;//返回List集合，list集合存储了查询的结果

		} finally {
			if (connection != null) {
				connection.close();
			}
		}
	}
}
